﻿using SwenAssigment.Classes;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace SwenAssigment.DBClasses
{
    public class StaffDBManager
    {
        public static int InsertStaff(Staff m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Staff] (Staff_Name,NRIC,DOB,Bank_Account,Address,Contact_Number,Job)" +
                    " VALUES (@StaffName,@NRIC,@DOB,@BankAccount,@Address,@ContactNumber,@Job)";
                comm.Parameters.AddWithValue("@StaffName", m.StaffName);
                comm.Parameters.AddWithValue("@NRIC", m.NRIC);
                comm.Parameters.AddWithValue("@DOB", m.Dob);
                comm.Parameters.AddWithValue("@BankAccount", m.BankAccountNumber);
                comm.Parameters.AddWithValue("@Address", m.Address);
                comm.Parameters.AddWithValue("@ContactNumber", m.ContactNumber);
                comm.Parameters.AddWithValue("@Job", m.Job);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static ArrayList GetAllStaff()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from [Staff]";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff m = new Staff();
                    m.StaffId = (int)dr["Staff_ID"];
                    m.StaffName = (string)dr["Staff_Name"];
                    m.NRIC = (string)dr["NRIC"];
                    m.Dob = (DateTime)dr["DOB"];
                    m.BankAccountNumber = (string)dr["Bank_Account"];
                    m.Address = (string)dr["Address"];
                    m.ContactNumber = (int)dr["Contact_Number"];
                    m.Job = (string)dr["Job"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList GetStaffsByName(string StaffName)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Staff]WHERE Staff_Name=@StaffName";
                comm.Parameters.AddWithValue("@StaffName", StaffName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff m = new Staff();
                    m.StaffId = (int)dr["Staff_ID"];
                    m.StaffName = (string)dr["Staff_Name"];
                    m.NRIC = (string)dr["NRIC"];
                    m.Dob = (DateTime)dr["DOB"];
                    m.BankAccountNumber = (string)dr["Bank_Account"];
                    m.Address = (string)dr["Address"];
                    m.ContactNumber = (int)dr["Contact_Number"];
                    m.Job = (string)dr["Job"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList GetStaffsByJob(string Job)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Staff]WHERE Job=@Job";
                comm.Parameters.AddWithValue("@Job", Job);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff m = new Staff();
                    m.StaffId = (int)dr["Staff_ID"];
                    m.StaffName = (string)dr["Staff_Name"];
                    m.NRIC = (string)dr["NRIC"];
                    m.Dob = (DateTime)dr["DOB"];
                    m.BankAccountNumber = (string)dr["Bank_Account"];
                    m.Address = (string)dr["Address"];
                    m.ContactNumber = (int)dr["Contact_Number"];
                    m.Job = (string)dr["Job"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Staff GetStaffByNric(string NRIC)
        {
            Staff result = new Staff();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Staff]WHERE NRIC=@NRIC";
                comm.Parameters.AddWithValue("@NRIC", NRIC);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result.StaffId = (int)dr["Staff_ID"];
                    result.StaffName = (string)dr["Staff_Name"];
                    result.NRIC = (string)dr["NRIC"];
                    result.Dob = (DateTime)dr["DOB"];
                    result.BankAccountNumber = (string)dr["Bank_Account"];
                    result.Address = (string)dr["Address"];
                    result.ContactNumber = (int)dr["Contact_Number"];
                    result.Job = (string)dr["Job"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static Staff GetStaffById(int Id)
        {
            Staff result = new Staff();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Staff]WHERE Staff_ID=@Staff_ID";
                comm.Parameters.AddWithValue("@Staff_ID", Id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result.StaffId = (int)dr["Staff_ID"];
                    result.StaffName = (string)dr["Staff_Name"];
                    result.NRIC = (string)dr["NRIC"];
                    result.Dob = (DateTime)dr["DOB"];
                    result.BankAccountNumber = (string)dr["Bank_Account"];
                    result.Address = (string)dr["Address"];
                    result.ContactNumber = (int)dr["Contact_Number"];
                    result.Job = (string)dr["Job"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static int UpdateStaff(Staff m)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [Staff]SET Staff_Name=@staff_name,DOB=@dob, Bank_Account=@bank_account, Address=@address, Contact_Number=@contact_number,Job = @job, NRIC = @nric where Staff_ID=@staff_id";
                comm.Parameters.AddWithValue("@staff_id", m.StaffId);
                comm.Parameters.AddWithValue("@staff_name", m.StaffName);
                comm.Parameters.AddWithValue("@dob", m.Dob);
                comm.Parameters.AddWithValue("@bank_account", m.BankAccountNumber);
                comm.Parameters.AddWithValue("@address", m.Address);
                comm.Parameters.AddWithValue("@contact_number", m.ContactNumber);
                comm.Parameters.AddWithValue("@job", m.Job);
                comm.Parameters.AddWithValue("@nric", m.NRIC);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStaff(int StaffID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from [Staff] where Staff_ID=@staff_id";
                comm.Parameters.AddWithValue("@staff_id", StaffID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int InsertStaffGeneralDuty(int Id)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [General_Maintenance] (Staff_ID)" +
                    " VALUES (@StaffID)";
                comm.Parameters.AddWithValue("@StaffID", Id);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertStaffRoomDuty(int Id)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Room_Maintenance] (Staff_ID)" +
                    " VALUES (@StaffID)";
                comm.Parameters.AddWithValue("@StaffID", Id);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertStaffEstateDuty(int Id)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Estate_Maintenance] (Staff_ID)" +
                    " VALUES (@StaffID)";
                comm.Parameters.AddWithValue("@StaffID", Id);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertStaffSecurityDuty(int Id)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Security] (Staff_ID)" +
                    " VALUES (@StaffID)";
                comm.Parameters.AddWithValue("@StaffID", Id);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static Boolean GetStaffByGeneralDuty(int Id)
        {
            Boolean result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [General_Maintenance]WHERE Staff_ID=@Staff_ID";
                comm.Parameters.AddWithValue("@Staff_ID", Id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Boolean GetStaffByRoomDuty(int Id)
        {
            Boolean result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Room_Maintenance]WHERE Staff_ID=@Staff_ID";
                comm.Parameters.AddWithValue("@Staff_ID", Id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Boolean GetStaffByEstateDuty(int Id)
        {
            Boolean result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Estate_Maintenance]WHERE Staff_ID=@Staff_ID";
                comm.Parameters.AddWithValue("@Staff_ID", Id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Boolean GetStaffBySecurityDuty(int Id)
        {
            Boolean result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Security]WHERE Staff_ID=@Staff_ID";
                comm.Parameters.AddWithValue("@Staff_ID", Id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static int DeleteStaffByGeneralDuty(int Id)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from [General_Maintenance] where Staff_ID=@staff_id";
                comm.Parameters.AddWithValue("@staff_id", Id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteStaffByRoomDuty(int Id)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from [Room_Maintenance] where Staff_ID=@staff_id";
                comm.Parameters.AddWithValue("@staff_id", Id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteStaffByEstateDuty(int Id)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from [Estate_Maintenance] where Staff_ID=@staff_id";
                comm.Parameters.AddWithValue("@staff_id", Id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteStaffBySecurityDuty(int Id)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from [Security] where Staff_ID=@staff_id";
                comm.Parameters.AddWithValue("@staff_id", Id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
    }
}